package Persistencia;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;


import Modelo.Adicional;
import Modelo.Base;
import Modelo.Cliente;
import Modelo.Dato;
import Modelo.PaquetedeServicios;
import Modelo.Plan;
import Modelo.Prepago;
import Modelo.SMS;
import Modelo.Servicio;
import Modelo.Voz;


public class AdministradorPersistenciaPlanes 
{
	private static AdministradorPersistenciaPlanes pool;
	
	
	private AdministradorPersistenciaPlanes()
	{
		
	}
	
	public static AdministradorPersistenciaPlanes getInstancia()
	{
		if (pool== null)
			pool =new AdministradorPersistenciaPlanes();
		return pool;
	}
	
	
	public void insertPlanAdic(Adicional unPlan)
	{
		//Obtener conexion BD 
		Connection con = PoolConnection.getPoolConnection().getConnection();
		
		//Generar sentencia SQL
		try
		{
			//Preparar sentencia insert en tabla destino
			String senten = "INSERT INTO Planes (id, nombre, tipo) VALUES (?,?,?)" ;
			String sentenAdic = "INSERT INTO PLAN_ADICIONAL (plan_base_id, costo_minuto_exc) VALUES (?,?)" ;
			String sentenSrvPlan = "INSERT INTO PLAN_SERVICIOS (plan_base_id, servicio_id) VALUES (?,?)" ;
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setInt(1, unPlan.getNroPlan());
			ps.setString(2,unPlan.getNombre());
			ps.setString(3,unPlan.getTipoPlan());
			ps.execute();		
			PreparedStatement psAdic = null;
			psAdic = con.prepareStatement(sentenAdic);
			psAdic.setInt(1, unPlan.getNroPlan());
			psAdic.setFloat(2,unPlan.getCostoVozMinExc());
			psAdic.execute();
			for (int i = 0; i < unPlan.getServicios().size(); i++) 
			{ 
				PreparedStatement psSrvPlan = null;
				psSrvPlan = con.prepareStatement(sentenSrvPlan);				
				psSrvPlan.setInt(1,unPlan.getNroPlan());
				psSrvPlan.setInt(2, unPlan.getServicios().elementAt(i).getId());
				psSrvPlan.execute();
			} 
					
			PoolConnection.getPoolConnection().realeaseConnection(con);
		
		}
	      catch( SQLException e ) 
	      {
				System.out.println("Mensaje Error al Insertar Plan: " + e.getMessage());
				System.out.println("Stack Trace al Insertar Plan: " + e.getStackTrace());
				PoolConnection.getPoolConnection().realeaseConnection(con);
	      }
	      
	}
	
	

	public void insertPlan(Plan unPlan)
	{
		//Obtener conexion BD 
		Connection con = PoolConnection.getPoolConnection().getConnection();
		
		//Generar sentencia SQL
		try
		{
			//Preparar sentencia insert en tabla destino
			String senten = "INSERT INTO Planes (id, nombre, tipo) VALUES (?,?,?)" ;
			String sentenSrvPlan = "INSERT INTO PLAN_SERVICIOS (plan_base_id, servicio_id) VALUES (?,?)" ;
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setInt(1, unPlan.getNroPlan());
			ps.setString(2,unPlan.getNombre());
			ps.setString(3,unPlan.getTipoPlan());
			ps.execute();		
			for (int i = 0; i < unPlan.getServicios().size(); i++) 
			{ 
				PreparedStatement psSrvPlan = null;
				psSrvPlan = con.prepareStatement(sentenSrvPlan);				
				psSrvPlan.setInt(1,unPlan.getNroPlan());
				psSrvPlan.setInt(2, unPlan.getServicios().elementAt(i).getId());
				psSrvPlan.execute();
			} 
					
			PoolConnection.getPoolConnection().realeaseConnection(con);
		
		}
	      catch( SQLException e ) 
	      {
				System.out.println("Mensaje Error al Insertar Plan: " + e.getMessage());
				System.out.println("Stack Trace al Insertar Plan: " + e.getStackTrace());
				PoolConnection.getPoolConnection().realeaseConnection(con);
	      }
	      
	}
	

	

	
	public Vector<Plan> cargaPlanes()
	{
		//Obtener conexion BD 
		Connection con = PoolConnection.getPoolConnection().getConnection();
		Vector<Plan> listaPlanes = new Vector<Plan>();
		float  costoExdMinAdic = 0;
		//Generar sentencia SQL
		try
		{
			//Preparar sentencia insert en tabla destino
			
			String senten = "select * from Planes";
			String sentenPlanSrv = "select * from PLAN_SERVICIOS where plan_base_id = ?";
			String sentenPlanAdic = "select * from PLAN_ADICIONAL where plan_base_id = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ResultSet result = ps.executeQuery();
			while (result.next())
			
			{   
				//System.out.print(result.getString("tipo"));
				if (result.getString("tipo").equalsIgnoreCase("BASE"))
				{
					Base unPlanbase = new Base( result.getString("nombre"), result.getInt("id"), result.getString("tipo"));
					PreparedStatement psPlanSrv = null;
					psPlanSrv = con.prepareStatement(sentenPlanSrv);
					psPlanSrv.setInt(1, result.getInt("id"));
					ResultSet resultPlanSrv = psPlanSrv.executeQuery();
					while (resultPlanSrv.next())						
					{ 
						unPlanbase.addServicios(AdministradorPersistenciaServico.getInstancia().buscarServicioporId(resultPlanSrv.getInt("servicio_id")));
					}
					listaPlanes.add(unPlanbase);
					
				}
				else if 
				(result.getString("tipo").equalsIgnoreCase("PREPAGO"))
				{
					Prepago unPlanPrepago = new Prepago( result.getString("nombre"), result.getInt("id"), result.getString("tipo"));
					PreparedStatement psPlanSrv = null;
					psPlanSrv = con.prepareStatement(sentenPlanSrv);
					psPlanSrv.setInt(1, result.getInt("id"));
					ResultSet resultPlanSrv = psPlanSrv.executeQuery();
					while (resultPlanSrv.next())						
					{ 
						unPlanPrepago.addServicios(AdministradorPersistenciaServico.getInstancia().buscarServicioporId(resultPlanSrv.getInt("servicio_id")));
					}
					listaPlanes.add(unPlanPrepago);
				}
				else if 
				(result.getString("tipo").equalsIgnoreCase("ADICIONAL"))
				{
					PreparedStatement psPlanAdic = null;
					psPlanAdic = con.prepareStatement(sentenPlanAdic);
					psPlanAdic.setInt(1, result.getInt("id"));
					ResultSet resultPlanAdic = psPlanAdic.executeQuery();
					while (resultPlanAdic.next())						
					{ 
					 costoExdMinAdic = resultPlanAdic.getFloat("costo_minuto_exc");
					}
					
					Adicional unPlanAdicional = new Adicional( result.getString("nombre"), result.getInt("id"), result.getString("tipo"), costoExdMinAdic);
					PreparedStatement psPlanSrv = null;
					psPlanSrv = con.prepareStatement(sentenPlanSrv);
					psPlanSrv.setInt(1, result.getInt("id"));
					ResultSet resultPlanSrv = psPlanSrv.executeQuery();
					while (resultPlanSrv.next())						
					{ 
						unPlanAdicional.addServicios(AdministradorPersistenciaServico.getInstancia().buscarServicioporId(resultPlanSrv.getInt("servicio_id")));
					}
					listaPlanes.add(unPlanAdicional);
				}
				
				
				
			}
			PoolConnection.getPoolConnection().realeaseConnection(con);
			return listaPlanes;	
		
			
		}
	      catch( SQLException e ) 
	      {
				System.out.println("Mensaje Error al Insertar Cliente: " + e.getMessage());
				System.out.println("Stack Trace al Insertar Cliente: " + e.getStackTrace());
				PoolConnection.getPoolConnection().realeaseConnection(con);
	      }
		return listaPlanes;
	      
	}
	
	
	

	public Plan getPlanPorId(int id)
	{
		//Obtener conexion BD 
		Connection con = PoolConnection.getPoolConnection().getConnection();
		Plan rPlans;
		float  costoExdMinAdic = 0;
		//Generar sentencia SQL
		try
		{
			//Preparar sentencia insert en tabla destino
			
			String senten = "select * from Planes where id = ?";
			String sentenPlanSrv = "select * from PLAN_SERVICIOS where plan_base_id = ?";
			String sentenPlanAdic = "select * from PLAN_ADICIONAL where plan_base_id = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setInt(1, id);
			ResultSet result = ps.executeQuery();
			while (result.next())
			
			{   
				//System.out.print(result.getString("tipo"));
				if (result.getString("tipo").equalsIgnoreCase("BASE"))
				{
					Base unPlanbase = new Base( result.getString("nombre"), result.getInt("id"), result.getString("tipo"));
					PreparedStatement psPlanSrv = null;
					psPlanSrv = con.prepareStatement(sentenPlanSrv);
					psPlanSrv.setInt(1, result.getInt("id"));
					ResultSet resultPlanSrv = psPlanSrv.executeQuery();
					while (resultPlanSrv.next())						
					{ 
						unPlanbase.addServicios(AdministradorPersistenciaServico.getInstancia().buscarServicioporId(resultPlanSrv.getInt("servicio_id")));
					}
					return unPlanbase;
					
				}
				else if 
				(result.getString("tipo").equalsIgnoreCase("PREPAGO"))
				{
					Prepago unPlanPrepago = new Prepago( result.getString("nombre"), result.getInt("id"), result.getString("tipo"));
					PreparedStatement psPlanSrv = null;
					psPlanSrv = con.prepareStatement(sentenPlanSrv);
					psPlanSrv.setInt(1, result.getInt("id"));
					ResultSet resultPlanSrv = psPlanSrv.executeQuery();
					while (resultPlanSrv.next())						
					{ 
						unPlanPrepago.addServicios(AdministradorPersistenciaServico.getInstancia().buscarServicioporId(resultPlanSrv.getInt("servicio_id")));
					}
					return unPlanPrepago;
				}
				else if 
				(result.getString("tipo").equalsIgnoreCase("ADICIONAL"))
				{
					PreparedStatement psPlanAdic = null;
					psPlanAdic = con.prepareStatement(sentenPlanAdic);
					psPlanAdic.setInt(1, result.getInt("id"));
					ResultSet resultPlanAdic = psPlanAdic.executeQuery();
					while (resultPlanAdic.next())						
					{ 
					 costoExdMinAdic = resultPlanAdic.getFloat("costo_minuto_exc");
					}
					
					Adicional unPlanAdicional = new Adicional( result.getString("nombre"), result.getInt("id"), result.getString("tipo"), costoExdMinAdic);
					PreparedStatement psPlanSrv = null;
					psPlanSrv = con.prepareStatement(sentenPlanSrv);
					psPlanSrv.setInt(1, result.getInt("id"));
					ResultSet resultPlanSrv = psPlanSrv.executeQuery();
					while (resultPlanSrv.next())						
					{ 
						unPlanAdicional.addServicios(AdministradorPersistenciaServico.getInstancia().buscarServicioporId(resultPlanSrv.getInt("servicio_id")));
					}
					return unPlanAdicional;
				}
				
				
				
			}
			PoolConnection.getPoolConnection().realeaseConnection(con);
			return null;	
		
			
		}
	      catch( SQLException e ) 
	      {
				System.out.println("Mensaje Error al Insertar Cliente: " + e.getMessage());
				System.out.println("Stack Trace al Insertar Cliente: " + e.getStackTrace());
				PoolConnection.getPoolConnection().realeaseConnection(con);
	      }
		return null;
	      
	}
	
	
	
	
}
